﻿using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.Extensions.Configuration;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.CustomerProfileConfig.Dto;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Entities.BPM_Engine;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.Utils;
using NP.BPMReportPlatform.YCLWLJGZSYC.Dto;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using Volo.Abp.Users;
using IndexedColors = NPOI.SS.UserModel.IndexedColors;
using Users = NP.BPMReportPlatform.Entities.BPM_Platform.Users;

namespace NP.BPMReportPlatform.IU
{
    public class IUAppService : BPMReportPlatformAppService, IIUAppService
    {
        private readonly SqlSugarClient _db, _dbEngine;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sqlSugarAppService"></param>
        public IUAppService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp, IBPMPlatformAppService iBPMPlatformApp)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
        }

        #region 委外检测流程 报表
        public async Task<PagedResultDto<GetOutsourceCheckOutput>> GetAllAsync(GetOutsourceCheckInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);

            listViewDto.ForEach(x => x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId));

            //返回结果
            return new PagedResultDto<GetOutsourceCheckOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        public async Task<List<GetOutsourceCheckOutput>> ExportAsync(GetOutsourceCheckInput input)
        {
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToListAsync();
            foreach (var item in listViewDto)
            {
                var userName = await _dbEngine.Queryable<Tasks>()
                        .Where(t=>t.InstanceNumber==item.InstanceNumber && t.Status==1)
                        .Select(t => t.UserName)
                        .FirstAsync();
                item.AttachmentsId = item.AttachmentsId.IsNullOrWhiteSpace() ? "" : GetAFullPath(item.AttachmentsId);
                item.CurrentApprover = userName;
            }
            //listViewDto.ForEach(x => x.AttachmentsId = x.AttachmentsId.IsNullOrWhiteSpace()? "": GetAFullPath(x.AttachmentsId) );
            return listViewDto;
        }


        private ISugarQueryable<GetOutsourceCheckOutput> GetQueryable(GetOutsourceCheckInput input)
        {
            var listQueryable = _db.Queryable<OutsourceCheckHeader,OutsourceCheckDetail, BusinessProcessesNew>(
                     (x,m, b) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == m.InstanceNumber, JoinType.Left, x.InstanceNumber == b.InstanceId))
                .WhereIF(!input.IsQueryAll && !string.IsNullOrWhiteSpace(input.Organization), x => x.Organization.Contains(input.Organization))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber == input.InstanceNumber)
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, m, b) => SqlFunc.ContainsArray(input.OrderStatus.ToArray(), b.ProcessStatusName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Contains(input.AddUserName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.BusinessOrg), x => x.BusinessOrg.Contains(input.BusinessOrg))
                .WhereIF(!string.IsNullOrWhiteSpace(input.Department), x => x.Department == input.Department)
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerName), x => x.CustomerName == input.CustomerName)
                .WhereIF(!string.IsNullOrWhiteSpace(input.SampleName), (x, m, b) => m.SampleName == input.SampleName)
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime <= input.EndCreationTime)
                .Select((x, m, b) => new GetOutsourceCheckOutput()
                {
                    InstanceNumber = x.InstanceNumber,
                    AddTime = x.AddTime,
                    AddUserName = x.AddUserName,
                    OrderStatus = b.ProcessStatusName,
                    Area = x.Area,
                    Organization=x.Organization,
                    BusinessOrg=x.BusinessOrg,
                    Department=x.Department,
                    ICYLSNo=x.ICYLSNo,
                    IsInstitution =x.IsInstitution,
                    CustomerType=x.CustomerType,
                    AssignorName =x.AssignorName,
                    CustomerNo=x.CustomerNo,
                    CustomerName=x.CustomerName,
                    CostCentre=x.CostCentre,
                    CostCentreName=x.CostCentreName,
                    GxzxName=x.GxzxName,
                    DkhjlName=x.DkhjlName,
                    JsjlName=x.JsjlName,
                    CurrentApprover = "",
                    FinishTime =x.FinishTime,
                    InvoicepersonName = x.InvoicepersonName,
                    IsReceivedInvoice = x.IsReceivedInvoice,
                    IsConsistentAmount = x.IsConsistentAmount,
                    Isscfp = x.Isscfp,
                    ScfpReason = x.ScfpReason,
                    Isybx =x.Isybx,
                    BxNo =x.BxNo,
                    ReportFinishDate = m.ReportFinishDate,
                    Institution=m.Institution,
                    SampleName =m.SampleName,
                    MaterialCode = m.MaterialCode,
                    ApplyType = m.ApplyType,
                    ProductType= m.ProductType,
                    ReferenceReq= m.ReferenceReq,
                    Project =m.Project,
                    CostEstimation =m.CostEstimation,
                    Remark =m.Remark,
                    AppointReason=m.AppointReason==null|| m.AppointReason=="" ? x.AppointReason : m.AppointReason,//指定理由先从明细表中找，没有就在主表中取
                    AppointPerson=m.AppointPerson,
                    Attachments =m.Attachments,
                    AttachmentsId=m.AttachmentsId,
                }).MergeTable().Distinct().OrderBy(ins => ins.AddTime, OrderByType.Desc);
            return listQueryable;
        }
        
        private string GetAFullPath(string guid)
        {
            var _downloadFileURLe = ConfigManager.DownloadFileURL+ guid;
            return _downloadFileURLe;
        }
        #endregion

        #region IU槽液检测录入 报表
        public async Task<PagedResultDto<GetCYJCOutput>> GetAllAsync(GetCYJCInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);

            listViewDto.ForEach(x => x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId));

            //返回结果
            return new PagedResultDto<GetCYJCOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        public async Task<List<GetCYJCOutput>> ExportAsync(GetCYJCInput input)
        {
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToListAsync();
            foreach (var item in listViewDto)
            {
                var userName = await _dbEngine.Queryable<Tasks>()
                        .Where(t => t.InstanceNumber == item.InstanceNumber && t.Status == 1)
                        .Select(t => t.UserName)
                        .FirstAsync();
            }
            return listViewDto;
        }


        private ISugarQueryable<GetCYJCOutput> GetQueryable(GetCYJCInput input)
        {
            var currentUserId = Guid.Empty;
            if (!input.IsQueryAll)
            {
                var currentUserDto = _dbPlatform.Queryable<Users>().Where(x => x.WorkNumber.Equals(CurrentUser.UserName)).First();
                if (currentUserDto != null)
                {
                    currentUserId = currentUserDto.UserId;
                }
            }
            var listQueryable = _db.Queryable<CYJCHeader, CYJCDetail, BusinessProcessesNew>(
                     (x, m, b) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == m.InstanceNumber, JoinType.Inner, x.InstanceNumber == b.InstanceId))
                .Where((x, m, b) =>b.ProcessStatusCode == "approved")
                .WhereIF(!input.IsQueryAll, x => x.AddUserId.Equals(currentUserId) || x.JSDDCode.Equals(CurrentUser.UserName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber == input.InstanceNumber)
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Contains(input.AddUserName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.Business), x => x.Business==input.Business)
                .WhereIF(!string.IsNullOrWhiteSpace(input.Area), x => x.Area == input.Area)
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerCode), x => x.CustomerCode.Contains(input.CustomerCode)|| x.CustomerName.Contains(input.CustomerCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ProductionLine), (x, m, b) => x.ProductionLine.Contains(input.ProductionLine))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ProductName), (x, m, b) => x.ProductName.Contains(input.ProductName))
               .WhereIF(!string.IsNullOrWhiteSpace(input.JSDDName), x => x.JSDDName.Contains(input.JSDDName))
               .WhereIF(!string.IsNullOrWhiteSpace(input.XSDDName), x => x.XSDDName.Contains(input.XSDDName))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime <= input.EndCreationTime)
                .Select((x, m, b) => new GetCYJCOutput()
                {
                    InstanceNumber = x.InstanceNumber,
                    AddTime = x.AddTime,
                    AddUserName = x.AddUserName,
                    Business = x.Business,
                    Industry = x.Industry,
                    Area = x.Area,
                    CustomerType = x.CustomerType,
                    CustomerCode = x.CustomerCode,
                    CustomerName = x.CustomerName,
                    CustomerAttribution = x.CustomerAttribution,
                    ProductionLine = x.ProductionLine,
                    ProductName = x.ProductName,
                    SDFCode2 = x.SDFCode2,
                    SDFName2 = x.SDFName2,
                    State = x.State,
                    JYDate = x.JYDate,
                    TCORHC = x.TCORHC,
                    Province = x.Province,
                    City = x.City,
                    TestingFrequency = x.TestingFrequency,
                    JSDDName = x.JSDDName,
                    XSDDName = x.XSDDName,
                    SamplingDate = x.SamplingDate,
                    TestingDate = x.TestingDate,

                }).MergeTable().Distinct().OrderBy(ins => ins.AddTime, OrderByType.Desc);
            return listQueryable;
        }
        #endregion

        #region IU槽液检测分析 报表
        public async Task<IWorkbook> ExportAsync(GetCYJCFXInput input)
        {

            var currentUserId = Guid.Empty;
            if (!input.IsQueryAll)
            {
                var currentUserDto = _dbPlatform.Queryable<Users>().Where(x => x.WorkNumber.Equals(CurrentUser.UserName)).First();
                if (currentUserDto != null)
                {
                    currentUserId = currentUserDto.UserId;
                }
            }
            var dataList =await _db.Queryable<CYJCHeader, BusinessProcessesNew>(
               (x,  b) => new JoinQueryInfos(JoinType.Inner, x.InstanceNumber == b.InstanceId))
                .Where((x, b)=>b.ProcessStatusCode == "approved")
                .WhereIF(!input.IsQueryAll, x => x.AddUserId.Equals(currentUserId) || x.JSDDCode.Equals(CurrentUser.UserName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber == input.InstanceNumber)
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Contains(input.AddUserName))
                .WhereIF(!string.IsNullOrWhiteSpace(input.Business), x => x.Business == input.Business)
                .WhereIF(!string.IsNullOrWhiteSpace(input.Area), x => x.Area == input.Area)
                .WhereIF(!string.IsNullOrWhiteSpace(input.CustomerCode), x => x.CustomerCode.Contains(input.CustomerCode) || x.CustomerName.Contains(input.CustomerCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ProductionLine), x=> x.ProductionLine.Contains(input.ProductionLine))
                .WhereIF(!string.IsNullOrWhiteSpace(input.ProductName), x => x.ProductName.Contains(input.ProductName))
               .WhereIF(!string.IsNullOrWhiteSpace(input.JSDDName), x => x.JSDDName.Contains(input.JSDDName))
               .WhereIF(!string.IsNullOrWhiteSpace(input.XSDDName), x => x.XSDDName.Contains(input.XSDDName))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null, x => x.AddTime >= input.StartCreationTime && x.AddTime <= input.EndCreationTime)
               .Select((x, b) => x).MergeTable().Distinct().OrderBy(x=>x.AddTime).ToListAsync() ;
            if (dataList.Count>0)
            {
                return await ExportData(dataList);
                
            }
            else
            {
                return null;
            }
        }

        public async Task<IWorkbook> ExportData(List<CYJCHeader> dataList)
        {
            // 创建一个新的工作簿
            var workbook = new XSSFWorkbook();
            // 创建一个工作表
            ISheet sheet = workbook.CreateSheet("槽液检测分析报表");
            #region // 标题样式
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle titleStyle = workbook.CreateCellStyle();
            //titleStyle.FillPattern = FillPattern.SolidForeground;
            titleStyle.SetFont(font);
            titleStyle.BorderBottom = BorderStyle.Thin;
            titleStyle.BorderLeft = BorderStyle.Thin;
            titleStyle.BorderRight = BorderStyle.Thin;
            titleStyle.BorderTop = BorderStyle.Thin;
            #endregion
            #region // 表头样式
            IFont font1 = workbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle headerStyle = workbook.CreateCellStyle();
            headerStyle.FillPattern = FillPattern.SolidForeground;
            headerStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;
            headerStyle.SetFont(font1);
            headerStyle.VerticalAlignment = VerticalAlignment.Center;
            headerStyle.Alignment = HorizontalAlignment.Center;
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BorderLeft = BorderStyle.Thin;
            headerStyle.BorderRight = BorderStyle.Thin;
            headerStyle.BorderTop = BorderStyle.Thin;
            #endregion
            #region // 内容样式
            IFont font2 = workbook.CreateFont();
            font2.Boldweight = (short)FontBoldWeight.Normal;
            ICellStyle dataStyle = workbook.CreateCellStyle();
            //dataStyle.FillPattern = FillPattern.SolidForeground;
            //titleInnerStyle.FillForegroundColor = IndexedColors.LightBlue.Index;
            dataStyle.SetFont(font2);
            dataStyle.VerticalAlignment = VerticalAlignment.Center;
            dataStyle.Alignment = HorizontalAlignment.Center;
            dataStyle.BorderBottom = BorderStyle.Thin;
            dataStyle.BorderLeft = BorderStyle.Thin;
            dataStyle.BorderRight = BorderStyle.Thin;
            dataStyle.BorderTop = BorderStyle.Thin;
            #endregion
            var headerDataList = dataList.GroupBy(x => new { x.CustomerName, x.ProductionLine }).ToList();
            var headerIndex = 0;
            var detailIndex = 0;
            foreach (var headerData in headerDataList)
            {
                var headers = headerData.OrderBy(x => x.AddTime).ToList();
                if (headers == null)
                {
                    continue;
                }
                //单独创建标题行
                var titleRow = sheet.CreateRow(headerIndex + detailIndex);
                titleRow.CreateCell(0).SetCellValue(headers[0].CustomerName + "/" + headers[0].ProductionLine);
                titleRow.GetCell(0).CellStyle = titleStyle;
                //sheet.SetColumnWidth(1, 25 * 256);
                headerIndex++;
                var headerRow = sheet.CreateRow(headerIndex+ detailIndex);
                #region // 创建表头
                var headerColumns = new List<string>
                    {
                         "序号",
                        "实验项目1",
                        "实验项目2",
                        "实验项目3",
                        "最小值",
                        "最大值",
                        "标准值",
                        "标准槽液检测频次/月"
                    };
                headerIndex++;
                var headerRow1 = sheet.CreateRow(headerIndex + detailIndex);// 样品日期
                headerIndex++;
                var headerRow2 = sheet.CreateRow(headerIndex + detailIndex);// 检测日期
                headerIndex++;
                var headerRow3 = sheet.CreateRow(headerIndex + detailIndex);// 对策
                headerRow1.CreateCell(0).SetCellValue("样品日期");
                headerRow2.CreateCell(0).SetCellValue("检测日期");
                headerRow3.CreateCell(0).SetCellValue("对策");
                
                 

                var index1 = 7;
                //detailIndex++;
                //var innerHeaderRow = sheet.CreateRow(headerIndex + detailIndex);//子行表头
                var newInstanceNumber = headers[headers.Count - 1].InstanceNumber;
                var newDetails = _db.Queryable<CYJCDetail>().Where(m => m.InstanceNumber == newInstanceNumber).OrderBy(m=>m.SortNo).ToList();
                foreach (var header in headers)
                {
                    index1++;
                    var details = _db.Queryable<CYJCDetail>().Where(x => x.InstanceNumber == header.InstanceNumber).ToList();
                    header.Details = details.OrderBy(x => x.SortNo).ToList() ;
                    headerColumns.Add(header.AddTime.ToString("yyyy/MM/dd"));
                    headerRow1.CreateCell(7).SetCellValue(header.TestingFrequency);
                    headerRow1.CreateCell(index1).SetCellValue(header.SamplingDate.Replace("-","/"));
                    headerRow2.CreateCell(index1).SetCellValue(header.TestingDate.Replace("-","/"));
                    headerRow3.CreateCell(index1).SetCellValue(header.Countermeasure);
                    headerRow1.GetCell(index1).CellStyle = dataStyle;
                    headerRow2.GetCell(index1).CellStyle = dataStyle;
                    headerRow3.GetCell(index1).CellStyle = dataStyle;
                }
                
                for (var i = 0; i < newDetails.Count; i++)
                {
                    detailIndex++;
                    var innerRow = sheet.CreateRow(headerIndex + detailIndex); // 创建子表明细行
                    innerRow.CreateCell(0).SetCellValue(newDetails[i].SortNo);
                    innerRow.CreateCell(1).SetCellValue(newDetails[i].Project1);
                    innerRow.CreateCell(2).SetCellValue(newDetails[i].Project2);
                    innerRow.CreateCell(3).SetCellValue(newDetails[i].Project3);
                    innerRow.CreateCell(4).SetCellValue(newDetails[i].MinValue);
                    innerRow.CreateCell(5).SetCellValue(newDetails[i].MaxValue);
                    innerRow.CreateCell(6).SetCellValue(newDetails[i].StandardValue);
                    innerRow.CreateCell(7).SetCellValue("");
                    //innerRow.GetCell(0).CellStyle = dataStyle;
                    //innerRow.GetCell(1).CellStyle = dataStyle;
                    //innerRow.GetCell(2).CellStyle = dataStyle;
                    //innerRow.GetCell(3).CellStyle = dataStyle;
                    //innerRow.GetCell(4).CellStyle = dataStyle;
                    //innerRow.GetCell(5).CellStyle = dataStyle;
                    //innerRow.GetCell(6).CellStyle = dataStyle;
                    var y = 1;
                    foreach (var header in headers)
                    {
                        var currentItem = header.Details.Where(m => m.SortNo == newDetails[i].SortNo).FirstOrDefault();
                        if (currentItem != null)
                        {
                            var currentRecordValue = header.Details.Where(m => m.SortNo == newDetails[i].SortNo)?.First()?.RecordValue;
                            innerRow.CreateCell(7 + y).SetCellValue(currentRecordValue);
                            innerRow.GetCell(7 + y).CellStyle = dataStyle;
                        }
                        y++;
                    }
                    for (var m = 0; m < headerColumns.Count; m++)
                    {
                        if (innerRow.GetCell(m) == null)
                        {
                            innerRow.CreateCell(m).SetCellValue("");
                        }
                        innerRow.GetCell(m).CellStyle = dataStyle;
                    }
                }
                for (var i = 0; i < headerColumns.Count; i++)
                {
                    sheet.SetColumnWidth(i, 15 * 256);
                    headerRow.CreateCell(i).SetCellValue(headerColumns[i]);
                    headerRow.GetCell(i).CellStyle = headerStyle;
                    if (headerRow1.GetCell(i) == null)
                    {
                        headerRow1.CreateCell(i).SetCellValue("");
                    }
                    headerRow1.GetCell(i).CellStyle = dataStyle;
                    if (headerRow2.GetCell(i) == null)
                    {
                        headerRow2.CreateCell(i).SetCellValue("");
                    }
                    
                    headerRow2.GetCell(i).CellStyle = dataStyle;
                    if (headerRow3.GetCell(i) == null)
                    {
                        headerRow3.CreateCell(i).SetCellValue("");
                    }
                    headerRow3.GetCell(i).CellStyle = dataStyle;
                }
                #endregion
                headerIndex+=2;

            }


            return workbook;
        }
        #endregion
    }
}
